package org.tmsps.ne4spring.base;

import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.tmsps.ne4spring.base.ne.NePage;
import org.tmsps.ne4spring.base.ne.NeSort;
import org.tmsps.ne4spring.exception.NEServiceException;
import org.tmsps.ne4spring.orm.ClassUtil;
import org.tmsps.ne4spring.orm.MySQLUtil;
import org.tmsps.ne4spring.orm.ORMUtil;
import org.tmsps.ne4spring.orm.model.DataModel;
import org.tmsps.ne4spring.orm.param.NeParamList;
import org.tmsps.ne4spring.orm.param.NeParamTools;
import org.tmsps.ne4spring.utils.ChkUtil;
import org.tmsps.ne4spring.utils.GenerateLongUtil;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class BaseMySQLService implements IBaseService {
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	@Autowired
	private JdbcTemplate jt;

	@Override
	public int saveObj(DataModel model) {
		logger.info("save the model" + model.toJsonString());
		if (ChkUtil.isNull(model)) {
			logger.error("保存的对象不能为空");
			throw new NEServiceException("保存的对象不能为空");
		}

		// 获取对象中ID属性字段
		Field idField = ClassUtil.getIdField(model.getClass());
		// 获取ID的值
		Object idVal = ClassUtil.getClassVal(idField, model);
		// 如果ID字段为空，则生成一个主键
		if (ChkUtil.isNull(idVal)) {
			if (idField.getType() == String.class) {
				idVal = GenerateLongUtil.getInstance().nextId() + "";
				ClassUtil.setClassVal(idField, model, idVal);
			} else if (idField.getType() == long.class || idField.getType() == Long.class) {
				idVal = GenerateLongUtil.getInstance().nextId();
				ClassUtil.setClassVal(idField, model, idVal);
			}
		}
		// 获取Bean中的属性值
		final List<Object> vals = ClassUtil.getValuesPar(model);
		// 根据Bean生成插入语句
		String sql = MySQLUtil.getInsSQL(model.getClass());
		logger.info("sql:{} and paras is {}", sql, JSON.toJSONString(vals));
		return jt.update(sql, vals.toArray());
	}

	@Override
	public int saveObj(DataModel model, boolean sync) {
		logger.info("save the model" + model.toJsonString());
		if (sync) {
			int flag = this.saveObj(model);
			// 获取对象中ID属性字段
			Field idField = ClassUtil.getIdField(model.getClass());
			// 获取ID的值
			Object idVal = ClassUtil.getClassVal(idField, model);
			model = findById(idVal, model.getClass());
			return flag;
		} else {
			return this.saveObj(model);
		}
	}

	@Override
	@Transactional
	public void saveObjs(List<? extends DataModel> objs) {
		if (ChkUtil.listIsNull(objs)) {
			logger.error("保存的对象集合不能为空");
			throw new NEServiceException("保存的对象集合不能为空");
		}
		objs.forEach(model -> {
			this.saveObj(model);
		});
	}

	@Override
	public <T extends DataModel> T findById(Object idVal, Class<? extends DataModel> clazz) {
		// 主键为空，无法查询，返回null
		if (ChkUtil.isNull(idVal)) {
			logger.warn("主键为null,无法查询,返回null");
			return null;
		}
		String sql = MySQLUtil.getSelectSQL(clazz, false);
		logger.info(sql);
		return jt.query(sql, new Object[] { idVal }, new ResultSetExtractor<T>() {
			@SuppressWarnings("unchecked")
			@Override
			public T extractData(ResultSet rs) throws SQLException, DataAccessException {
				while (rs.next()) {
					T obj = ((T) MySQLUtil.fillPojoByResultSet(rs, clazz));
					// 只取第一个
					return (T) obj;
				}
				return null;
			}
		});
	}

	@Override
	public List<Map<String, Object>> findList(String sql, Object... vals) {
		logger.info("{}   -->{}", sql, JSON.toJSONString(vals));
		return jt.queryForList(sql, vals);
	}

	@Override
	public Map<String, Object> findMap(String sql, Object... vals) {
		logger.info("{}   -->{}", sql, JSON.toJSONString(vals));
		List<Map<String, Object>> list = this.findList(sql, vals);
		if (ChkUtil.listIsNotNull(list)) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public int updateObj(DataModel obj) {
		if (ChkUtil.isNull(obj)) {
			logger.error("update faile , the obj can not null !");
			return 0;
		} else {
			String sql = MySQLUtil.getUpdateSQL(obj.getClass());
			final LinkedHashMap<Object, Object> clsky = ClassUtil.getClassKeyValWithoutPk(obj);
			List<Object> vals = new ArrayList<Object>();
			clsky.entrySet().forEach(entry -> {
				vals.add(entry.getValue());
			});
			Object pkVal = ClassUtil.getClassVal(ClassUtil.getIdField(obj.getClass()), obj);
			vals.add(pkVal);
			logger.info("sql:{} and paras is {}", sql, JSON.toJSONString(vals));
			return jt.update(sql, vals.toArray());
		}
	}

	@Override
	public List<Map<String, Object>> findList(String sql, NeParamList params) {
		String endSql = NeParamTools.handleSql(sql, params);
		logger.info("{}   -->{}", endSql, params.toString());
		return jt.queryForList(endSql, params.getParamValues());
	}

	@Override
	public <T> List<T> findList(Class<T> clazz, String sql, Object... vals) {
		List<Map<String, Object>> list = this.findList(sql, vals);
		return ORMUtil.fillMapToList(list, clazz);
	}

	@Override
	public <T extends DataModel> T findObj(Class<T> clazz, String sql, Object... vals) {
		Map<String, Object> map = this.findMap(sql, vals);
		return ORMUtil.fillMapToAnyBean(clazz, map);
	}

	@Override
	public <T> List<T> findList(Class<T> clazz, NePage page, String sql, Map<String, String> sort_params,
                                Object... vals) {
		List<Map<String, Object>> list = this.findList(sql, vals, page);
		return ORMUtil.fillMapToList(list, clazz);
	}

	@Override
	public <T> List<T> findList(Class<T> clazz, NePage page, String sql, Object... vals) {
		List<Map<String, Object>> list = this.findList(sql, vals, page);
		return ORMUtil.fillMapToList(list, clazz);
	}

	@Override
	public <T> List<T> findList(Class<T> clazz, String sql, NeParamList params) {
		List<Map<String, Object>> list = this.findList(sql, params);
		return ORMUtil.fillMapToList(list, clazz);
	}

	// ***************** NePage版本
	// ***************************************************
	@Override
	public NePage queryForNePage(String sql, NePage page, Object... vals) {
		Integer position = sql.toLowerCase().indexOf("from ");
		String sql_count = "select count(*) cnt " + sql.substring(position);
		position = sql_count.toLowerCase().indexOf("order by");
		if (position > 0) {
			sql_count = sql_count.substring(0, position);
		}
		return this.queryForNePage(sql, sql_count, page, vals);
	}

	@Override
	public NePage queryForNePage(String sql, String sql_cnt, NePage page, Object... vals) {
		List<Map<String, Object>> ret = null;
		int cnt = jt.queryForObject(sql_cnt, vals, Integer.class);
		page.setTotalRow(cnt);
		page.setTotalPage((int) (page.getTotalRow() - 1) / page.getPageSize() + 1);
		if (page.getPageNumber() > page.getTotalPage()) {
			page.setPageNumber(page.getTotalPage());
		}
		if (page.getPageNumber() <= 0) {
			page.setPageNumber(1);
		}
		// 分页
		sql += " limit :start,:pageSize";
		sql = sql.replace(":start", page.getPageSize() * (page.getPageNumber() - 1) + "");
		sql = sql.replace(":pageSize", page.getPageSize() + "");
		logger.info("{}   -->{}", sql, JSON.toJSONString(vals));
		ret = jt.queryForList(sql, vals);
		page.setList(ret);
		return page;
	}

	/**
	 * 内部分页的查询
	 * 
	 * @param sql
	 * @param vals
	 * @param page
	 * @return
	 */
	private NePage onlyqueryForNePage(String sql, final Object[] vals, final NePage page) {
		page.setTotalPage((int) (page.getTotalRow() - 1) / page.getPageSize() + 1);
		if (page.getPageNumber() > page.getTotalPage()) {
			page.setPageNumber(page.getTotalPage());
		}
		if (page.getPageNumber() <= 0) {
			page.setPageNumber(1);
		}
		// 分页
		sql += " limit :start,:pageSize";
		sql = sql.replace(":start", page.getPageSize() * (page.getPageNumber() - 1) + "");
		sql = sql.replace(":pageSize", page.getPageSize() + "");
		logger.info("{}   -->{}", sql, JSON.toJSONString(vals));
		List<Map<String, Object>> list = jt.queryForList(sql, vals);
		page.setList(list);
		return page;
	}

	/**
	 * private List<Map<String, Object>> onlyqueryForNePage(final String sql, final
	 * Object[] vals, final NePage page) {
	 * 
	 * List<Map<String, Object>> list = this.getHibernateTemplate() .execute(new
	 * HibernateCallback<List<Map<String, Object>>>() {
	 * 
	 * @Override public NePage doInHibernate(Session session) throws
	 *           HibernateException { NativeQuery<Map<String, Object>> query =
	 *           session.createNativeQuery(sql); //
	 *           query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); //
	 *           设置值 if (ChkUtil.isNotNull(vals)) { for (int i = 0; i < vals.length;
	 *           i++) { query.setParameter(i, vals[i]); } }
	 *           query.setFirstResult(page.getNewBeginResult());
	 *           query.setMaxResults(page.getPageSize()); List<Map<String, Object>>
	 *           list = query.list(); return list; } });
	 * 
	 *           return list; }
	 */

	/**
	 * 万能分页查询
	 */
	@Override
	public NePage queryForNePageUniversal(final String sql, NePage page, final Object... vals) {

		long count = jt.execute(sql, new PreparedStatementCallback<Long>() {

			@Override
			public Long doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
				if (ChkUtil.isNotNull(vals)) {

					for (int i = 0; i < vals.length; i++) {
						pstmt.setObject(i + 1, vals[i]);
					}
				}
				ResultSet rs = pstmt.executeQuery();
				rs.last();
				long count = rs.getRow();
				return count;
			}
		});

		page.setTotalRow(count);

		String sql_new = sql;
		return onlyqueryForNePage(sql_new, vals, page);

	}

	@Override
	public NePage queryForNePage(String sql, NeSort sorter, NePage page, Object... vals) {
		Integer position = sql.toLowerCase().indexOf("from ");
		String sql_count = "select count(*) cnt " + sql.substring(position);
		// sql_count 去掉order by 及后面的语句
		position = sql_count.toLowerCase().indexOf("order by");
		if (position > 0) {
			sql_count = sql_count.substring(0, position);
		}
		return this.queryForNePage(sql, sql_count, sorter, page, vals);
	}

	@Override
	public NePage queryForNePage(String sql, String sql_count, NeSort sorter, NePage page, Object... vals) {

		List<Map<String, Object>> list = jt.queryForList(sql_count, vals);

		long count = 0L;
		if (list.size() > 0) {
			Number num = (Number) list.get(0).get("cnt");
			count = num.longValue();
		}
		// 分页
		page.setTotalRow(count);

		// 排序
		if (sorter != null && !sorter.isNull()) {

			String ob = " order by";
			int orderIndex = sql.toLowerCase().indexOf(ob);
			if (orderIndex == -1) {
				sql += ob + sorter.getSql();
			} else {
				// 防止多条件排序
				sql = sql.substring(0, orderIndex);
				sql += ob + sorter.getSql();
			}
		} // #if
		return onlyqueryForNePage(sql, vals, page);
	}

	@Override
	public NePage queryForNePage(String sql, NeParamList params, NePage page) {
		return this.queryForNePage(sql, params, null, page);
	}

	@Override
	public NePage queryForNePage(String sql, NeParamList params, NeSort sorter, NePage page) {
		String endSql = NeParamTools.handleSql(sql, params);
		return this.queryForNePage(endSql, sorter, page, params.getParamValues());
	}

	@Override
	public int deleteById(Object id, Class<? extends DataModel> clazz) {
		DataModel model = this.findById(id, clazz);
		model.setStatus(-100);
		return this.updateObj(model);
	}
	
	@Override
	public int deleteRealById(Object id, Class<? extends DataModel> clazz) {
		final String sql = MySQLUtil.getDelRealSQL(clazz);
		logger.info("{}   -->{}", sql, id);
		return jt.update(sql, id);
	}

}
